package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Comment;
import pojo.Song;
import pojo.SongList;
import pojo.UpdatedData;
import pojo.User;

public class OperateDB implements InterfaceDB {
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	private Connection con = null;

	@Override
	public boolean addUser(User user) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "insert into user(userName,password,email,createdTime,modifiedTime) values(?,?,?,?,?)";

		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getPassword());
			pst.setString(3, user.getEmail());
			pst.setString(4, user.getCreatedTime());
			pst.setString(5, user.getModifiedTime());
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public User findUser(String userName, String password) {
		User user = null;
		con = new ConnectDB().getConn();
		String sql = "select * from user where userName=? and password=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, userName);
			pst.setString(2, password);
			rs = pst.executeQuery();
			while (rs.next()) {
				user = new User(rs.getInt("user.userId"), userName, password, rs.getString("email"),
						rs.getString("createdTime"), rs.getString("modifiedTime"));
			}
		} catch (

		SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return user;
	}

	@Override
	public User findUser(int uid) {
		User user = null;
		con = new ConnectDB().getConn();
		String sql = "select * from user where userId=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, uid);
			rs = pst.executeQuery();
			if (rs.next()) {
				user = new User(rs.getInt("userId"), rs.getString("userName"), rs.getString("password"),
						rs.getString("email"), rs.getString("createdTime"), rs.getString("modifiedTime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return user;
	}

	@Override
	public boolean addSongList(int userId, String songListName, String createdTime) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "insert into songList(userId,songListName,createdTime) values(?,?,?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, userId);
			pst.setString(2, songListName);
			pst.setString(3, createdTime);
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public boolean deleteSongList(int songListId) {
		boolean a = true;
		deleteSong_SongList(songListId);
		con = new ConnectDB().getConn();
		String sql = "delete from songList where songListId=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, songListId);
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public boolean updateSongList(int songListId, String songListName, String description, String modifiedTime) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "update songList set songListName=?,description=?,modifiedTime=? where songListId=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, songListName);
			pst.setString(2, description);
			pst.setString(3, modifiedTime);
			pst.setInt(4, songListId);
			pst.execute();

		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public List<Comment> findComment(int songId) {
		List<Comment> lists = new ArrayList<>();
		con = new ConnectDB().getConn();
		String sql = "select comment.*,userName from comment,user where user.userId=comment.userId and comment.songId=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, songId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Comment comment = new Comment(rs.getInt("commentId"), rs.getInt("userId"), rs.getInt("songId"),rs.getString("userName"),
						rs.getString("createdTime"), rs.getString("commentBody"));
				lists.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return lists;
	}

	@Override
	public boolean addCommnet(Comment comment) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "insert into comment(createdTime,commentBody,userId,songId) values(?,?,?,?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, comment.getCreatedTime());
			pst.setString(2, comment.getCommentBody());
			pst.setInt(3, comment.getUserId());
			pst.setInt(4, comment.getSongId());
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public boolean deleteComment(int commentId) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "delete from comment where commentId=?";
		try {

			pst = con.prepareStatement(sql);
			pst.setInt(1, commentId);
			pst.execute();

		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public boolean addSong_SongList(int songId, int songListId) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "insert into song_songList(songId,songListId) values(?,?)";

		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, songId);
			pst.setInt(2, songListId);
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public boolean deleteSong_SongList(int songId, int songListId) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "delete from song_songList where songId=? and songListId=?";
		try {

			pst = con.prepareStatement(sql);
			pst.setInt(1, songId);
			pst.setInt(2, songListId);
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}

	@Override
	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pst != null) {
				pst.close();
				pst = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<SongList> findSongLists(int userId) {
		List<SongList> lists = new ArrayList<>();
		PreparedStatement pst2 = null;
		ResultSet rs2 = null;
		Connection con2 = null;
		con = new ConnectDB().getConn();
		con2 = new ConnectDB().getConn();
		try {
			String sql = "select * from songList where userId=?";
			pst = con.prepareStatement(sql);
			pst.setInt(1, userId);
			rs = pst.executeQuery();
			while (rs.next()) {
				List<Integer> songIds = new ArrayList<>();
				String sql2 = "select songId from song_songList where songListId=?";
				pst2 = con2.prepareStatement(sql2);
				pst2.setInt(1, rs.getInt("songListId"));
				rs2 = pst2.executeQuery();
				while (rs2.next()) {
					songIds.add(rs2.getInt("songId"));
				}
				SongList songList = new SongList(rs.getInt("songListId"), rs.getInt("userId"), songIds,
						rs.getString("songListName"), rs.getString("createdTime"), rs.getString("description"),
						rs.getString("modifiedTime"));
				lists.add(songList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
			try {
				if (rs2 != null) {
					rs2.close();
					rs2 = null;
				}
				if (pst2 != null) {
					pst2.close();
					pst2 = null;
				}
				if (con2 != null) {
					con2.close();
					con2 = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return lists;
	}

	@Override
	public List<Song> findSongs(String title) {
		List<Song> lists = new ArrayList<>();
		PreparedStatement pst2 = null;
		ResultSet rs2 = null;
		Connection con2 = null;
		con = new ConnectDB().getConn();
		con2 = new ConnectDB().getConn();
		try {
			String sql = "select song.*,album.* from song,album,artist,artist_song where (title like ? || artistName like ? || albumName like ?) && song.albumId=album.albumId && song.songId=artist_song.songId && artist.artistId=artist_song.artistId limit 0,20";
			pst = con.prepareStatement(sql);
			pst.setString(1, "%" + title + "%");
			pst.setString(2, "%" + title + "%");
			pst.setString(3, "%" + title + "%");
			rs = pst.executeQuery();

			while (rs.next()) {
				List<Integer> artistIds = new ArrayList<>();
				List<String> artistNames = new ArrayList<>();
				String sql2 = "select artist.artistId,artistName from artist,artist_song where artist.artistId=artist_song.artistId and songId=? ";
				pst2 = con2.prepareStatement(sql2);
				pst2.setInt(1, rs.getInt("songId"));
				rs2 = pst2.executeQuery();
				while (rs2.next()) {
					artistIds.add(rs2.getInt("artist.artistId"));
					artistNames.add(rs2.getString("artistName"));
				}
				Song song = new Song(rs.getInt("songId"), rs.getString("title"), rs.getInt("song.albumId"),
						rs.getString("albumName"), artistIds, artistNames, rs.getString("songUrl"),
						rs.getString("lyricUrl"), rs.getString("pictureUrl"));
				lists.add(song);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
			try {
				if (rs2 != null) {
					rs2.close();
					rs2 = null;
				}
				if (pst2 != null) {
					pst2.close();
					pst2 = null;
				}
				if (con2 != null) {
					con2.close();
					con2 = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return lists;
	}

	@Override
	public List<Song> findSongsByUser(List<SongList> songLists) {
		List<Song> lists = new ArrayList<>();
		PreparedStatement pst2 = null;
		ResultSet rs2 = null;
		Connection con2 = null;
		con = new ConnectDB().getConn();
		con2 = new ConnectDB().getConn();
		String sql = "select song.*,album.* from song,song_songList,album where song.songId=song_songList.songId and song.albumId=album.albumId and songListId=?";
		try {
			for (int i = 0; i < songLists.size(); i++) {

				pst = con.prepareStatement(sql);
				pst.setInt(1, songLists.get(i).getSongListId());
				rs = pst.executeQuery();
				while (rs.next()) {
					List<Integer> artistIds = new ArrayList<>();
					List<String> artistNames = new ArrayList<>();
					String sql2 = "select artist.artistId,artistName from artist,artist_song where artist.artistId=artist_song.artistId and songId=? ";
					pst2 = con2.prepareStatement(sql2);
					pst2.setInt(1, rs.getInt("songId"));
					rs2 = pst2.executeQuery();
					while (rs2.next()) {
						artistIds.add(rs2.getInt("artist.artistId"));
						artistNames.add(rs2.getString("artistName"));
					}
					Song song = new Song(rs.getInt("songId"), rs.getString("title"), rs.getInt("song.albumId"),
							rs.getString("albumName"), artistIds, artistNames, rs.getString("songUrl"),
							rs.getString("lyricUrl"), rs.getString("pictureUrl"));
					lists.add(song);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
			try {
				if (rs2 != null) {
					rs2.close();
					rs2 = null;
				}
				if (pst2 != null) {
					pst2.close();
					pst2 = null;
				}
				if (con2 != null) {
					con2.close();
					con2 = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return lists;
	}

	@Override
	public UpdatedData findAllSongs(String userName, String password) {
		UpdatedData updatedData = new UpdatedData();
		User user = findUser(userName, password);
		List<SongList> songLists = findSongLists(user.getUserId());
		List<Song> songs = findSongsByUser(songLists);
		for (int i = 0; i < songs.size() - 1; i++) {
			for (int j = songs.size() - 1; j > i; j--) {
				if (songs.get(j).getSongId() == songs.get(i).getSongId()) {
					songs.remove(j);
				}
			}
		}
		updatedData.setUser(user);
		updatedData.setSongLists(songLists);
		updatedData.setSongs(songs);
		return updatedData;
	}

	@Override
	public UpdatedData findAllSongs(int userId) {
		UpdatedData updatedData = new UpdatedData();
		User user = findUser(userId);
		List<SongList> songLists = findSongLists(user.getUserId());
		List<Song> songs = findSongsByUser(songLists);
		for (int i = 0; i < songs.size() - 1; i++) {
			for (int j = songs.size() - 1; j > i; j--) {
				if (songs.get(j).getSongId() == songs.get(i).getSongId()) {
					songs.remove(j);
				}
			}
		}
		updatedData.setUser(user);
		updatedData.setSongLists(songLists);
		updatedData.setSongs(songs);
		return updatedData;
	}
	@Override
	public boolean deleteSong_SongList(int songListId) {
		boolean a = true;
		con = new ConnectDB().getConn();
		String sql = "delete from song_songList where songListId=?";
		try {

			pst = con.prepareStatement(sql);
			pst.setInt(1, songListId);
			pst.execute();
		} catch (SQLException e) {
			a = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return a;
	}
}
